/**
 * 
 */
package com.ffcs.crmd.tsp.server.jdbc.repository;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.ffcs.crmd.tsp.common.constant.LoggerName;
import com.ffcs.crmd.tsp.common.exception.TspException;
import com.ffcs.crmd.tsp.common.exception.TspTransactionException;
import com.ffcs.crmd.tsp.server.jdbc.ColConstants;

/**
 * 功能说明:jdbc服务抽象父类
 *
 * @author ZHONGFUHUA
 * 
 * @since 2.0.0-SNAPSHOT
 *
 */
public abstract class AbstractJdbcRepository implements ColConstants {
    
    /**
     * 日志
     */
    protected static final Logger    LOG       = LoggerFactory.getLogger(LoggerName.ServerLoggerName);
    
    protected final SimpleDateFormat formatter = new SimpleDateFormat("yy-MM-dd HH:mm:ss");
    
    /**
     * 
     * 功能说明:增加、删除、改 
     * 
     * @param sql 语句
     * @param params 参数
     * @return 执行结果是否成功
     * @throws TspTransactionException 
     */
    protected boolean execute(String sql, List<Object> params, Connection connection) throws TspException {
        boolean flag = false;
        int result = -1;
        PreparedStatement pstmt = null;
        try {
            pstmt = connection.prepareStatement(sql);
            int index = 1;
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            result = pstmt.executeUpdate();
            flag = result > 0 ? true
                : false;
        } catch (SQLException e) {
            LOG.error("DB-SERVER: execute sql script exception:", e);
            throw new TspException(e);
        } finally {
            closeStatement(pstmt);
        }
        return flag;
    }
    
    protected int queryRows(String sql, List<Object> params, Connection connection) throws TspException {
        int index = 1;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            pstmt = connection.prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            resultSet = pstmt.executeQuery();
            resultSet.last();
            return resultSet.getRow();
        } catch (SQLException e) {
            throw new TspException(e);
        } finally {
            closeResultSet(resultSet);
            closeStatement(pstmt);
        }
    }
    
    /**
     * 
     * 功能说明:查询单条记录
     * 
     * @param sql 语句
     * @param params 参数
     * @return 查询结果
     * @throws SQLException
     */
    protected Map<String, Object> querySingle(String sql, List<Object> params, Connection connection) throws TspException {
        Map<String, Object> map = new HashMap<String, Object>();
        int index = 1;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            pstmt = connection.prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            resultSet = pstmt.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int col_len = metaData.getColumnCount();
            while (resultSet.next()) {
                for (int i = 0; i < col_len; i++) {
                    String cols_name = metaData.getColumnLabel(i + 1);
                    Object cols_value = resultSet.getObject(cols_name);
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    map.put(cols_name, cols_value);
                }
            }
        } catch (SQLException e) {
            throw new TspException(e);
        } finally {
            closeResultSet(resultSet);
            closeStatement(pstmt);
        }
        return map;
    }
    
    /**
     * 
     * 功能说明:查询多条记录
     * 
     * @param sql 语句
     * @param params 参数
     * @return 查询结果
     * @throws SQLException
     */
    protected List<Map<String, Object>> queryBatch(String sql, List<Object> params, Connection connection) throws TspException {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int index = 1;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            pstmt = connection.prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            resultSet = pstmt.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int cols_len = metaData.getColumnCount();
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 0; i < cols_len; i++) {
                    String cols_name = metaData.getColumnLabel(i + 1);
                    Object cols_value = resultSet.getObject(cols_name);
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    map.put(cols_name, cols_value);
                }
                list.add(map);
            }
        } catch (SQLException e) {
            throw new TspException(e);
        } finally {
            closeResultSet(resultSet);
            closeStatement(pstmt);
        }
        return list;
    }
    
    /**
     * 
     * 功能说明:通过反射机制查询单条记录
     * 
     * @param sql 语句
     * @param params 参数
     * @param classes 结果类型
     * @return 查询结果
     * @throws Exception
     */
    protected <T> T querySingle(String sql, List<Object> params, Class<T> classes, Connection connection) throws TspException {
        T resultObject = null;
        int index = 1;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            pstmt = connection.prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            resultSet = pstmt.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int cols_len = metaData.getColumnCount();
            while (resultSet.next()) {
                //通过反射机制创建一个实例  
                resultObject = classes.newInstance();
                for (int i = 0; i < cols_len; i++) {
                    String cols_name = metaData.getColumnLabel(i + 1);
                    Object cols_value = resultSet.getObject(cols_name);
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    Field field = classes.getDeclaredField(cols_name);
                    field.setAccessible(true); //打开javabean的访问权限  
                    field.set(resultObject, cols_value);
                }
            }
        } catch (SQLException | InstantiationException | IllegalAccessException | NoSuchFieldException | SecurityException e) {
            throw new TspException(e);
        } finally {
            closeResultSet(resultSet);
            closeStatement(pstmt);
        }
        return resultObject;
        
    }
    
    /**
     * 
     * 功能说明:通过反射机制查询多条记录
     * 
     * @param sql 语句
     * @param params 参数
     * @param classes 结果类型
     * @return 查询结果
     * @throws Exception
     */
    protected <T> List<T> queryBatch(String sql, List<Object> params, Class<T> classes, Connection connection) throws TspException {
        List<T> list = new ArrayList<T>();
        int index = 1;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            pstmt = connection.prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            resultSet = pstmt.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int cols_len = metaData.getColumnCount();
            while (resultSet.next()) {
                //通过反射机制创建一个实例  
                T resultObject = classes.newInstance();
                for (int i = 0; i < cols_len; i++) {
                    String cols_name = metaData.getColumnLabel(i + 1);
                    Object cols_value = resultSet.getObject(cols_name);
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    Field field = classes.getDeclaredField(cols_name);
                    field.setAccessible(true); //打开javabean的访问权限  
                    field.set(resultObject, cols_value);
                }
                list.add(resultObject);
            }
        } catch (SQLException | InstantiationException | IllegalAccessException | NoSuchFieldException | SecurityException e) {
            throw new TspException(e);
        } finally {
            closeResultSet(resultSet);
            closeStatement(pstmt);
        }
        return list;
    }
    
    protected static void closeResultSet(ResultSet rs) {
        if (null != rs) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    protected static void closeStatement(Statement statement) {
        if (null != statement) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
